<!DOCTYPE html>
<html lang="zh-CN">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.ico">
  <link rel="icon" type="image/png" href="/img/favorite.ico">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="小九的日常笔记">
  <meta name="author" content="小九">
  <meta name="keywords" content="笔记，博客，前端，html，css，javascript，面试题，前端面试题">
  <title>sequelize基础Api - 小九的书柜</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->

  
<link rel="stylesheet" href="/css/my.css">



<!-- 
<link rel="stylesheet" href="/css/message.css">
 -->
<!-- 
<link rel="stylesheet" href="https://at.alicdn.com/t/font_1117508_wxidm5ry7od.css">
 -->

  <script  src="/js/utils.js" ></script>
<meta name="generator" content="Hexo 4.2.1"></head>



<body>
  
  <div id="background">
    <!--  -->
    <header style="height: 70vh;">
      <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
      href="/">&nbsp;<strong>jLongYan's blog</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
      data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false"
      aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/">
            <i class="iconfont icon-home-fill"></i>
            首页
          </a>
        </li>
        
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/archives/">
            <i class="iconfont icon-archive-fill"></i>
            归档
          </a>
        </li>
        
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/categories/">
            <i class="iconfont icon-category-fill"></i>
            分类
          </a>
        </li>
        
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/tags/">
            <i class="iconfont icon-tags-fill"></i>
            标签
          </a>
        </li>
        
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/links/">
            <i class="iconfont icon-link-fill"></i>
            友链
          </a>
        </li>
        
        
        
        
        
        
        

        <li class="nav-item 1 " </li>
          <a class="nav-link 1 " href="/about/">
            <i class="iconfont icon-user-fill"></i>
            关于
          </a>
        </li>
        
        
        
        <li class="nav-item" onclick="switchDarkMode()">
          <span id="dark"></span>
        </li>
        
      </ul>
    </div>
  </div>
</nav>
<style>
  .active {
    /* border-bottom: 1px solid #00FFFF; */
    color: #00FFFF !important;
  }
</style>

<script>
  var isNight = new Date().getHours() >= 22 || new Date().getHours() < 5; // 指定时间
  // 依次判断 系统暗黑模式 指定时间 缓存 dark
  if (matchMedia('(prefers-color-scheme: dark)').matches || isNight || localStorage.getItem('dark') === '1') {
    if (!(isNight && localStorage.getItem('noDark') === '1')) {
      document.body.classList.add('dark');
    }
  }
  //点击事件
  function switchDarkMode() {
    if ($('body').hasClass('dark')) {
      // checkWallaper()
      document.getElementById('dark').style.backgroundImage = 'url(/img/太阳.png)'
      document.body.classList.remove('dark');
      localStorage.setItem('noDark', '1');
      localStorage.setItem('dark', '0');
    } else {
      document.getElementById('dark').style.backgroundImage = 'url(/img/月亮.png)'
      document.body.classList.add('dark');
      localStorage.setItem('dark', '1');
      localStorage.setItem('noDark', '0');
      // checkWallaper()
    }
  }

</script>



      <!-- 如果需要采用配置文件的路径 在下面的div粘贴此段代码进去即可 目前采用的是 自定义的随机图片 -->
      <!-- style="background: url('/headImg/sequelize.jpg') no-repeat center center;background-size: cover;" -->
      <div class="view intro-2" id="indexBackground" parallax=true
        style='background-size:cover'>
        <div class="full-bg-img">
          <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.1)">
            <div class="container text-center white-text fadeInUp">

              <span class="h2" id="subtitle">
                
              </span>

              <div id="yiyan" onclick="watchYiyan()">只有极其努力，才能看起来毫不费力。</div>

              


              
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2020-07-15 16:38">
      2020年7月15日 下午
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      2.5k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      31
       分钟
    </span>
  

  
  
    
  
</div>
<!-- <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script> -->
              
            </div>

            
            <div class="scroll-down-bar">
              <i class="iconfont icon-arrowdown"></i>
            </div>
            
          </div>
        </div>
      </div>
    </header>
    <!--  -->



    <main>
      
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
              <p class="note note-info">
                
                  本文最后更新于：2021年1月27日 上午
                
              </p>
            
            <article class="markdown-body">
              <h3 id="sequelize"><a href="#sequelize" class="headerlink" title="sequelize"></a>sequelize</h3><p>目前有许许多多的<strong>ORM</strong>，但是目前最为流行的依然是<strong>sequelize</strong>，所以这里总结写之前自己写自己的博客所涉及到的点，分享给大家，让大家也可以少踩坑，更快入门。</p>
<h3 id="sequelize-cli的基本流程"><a href="#sequelize-cli的基本流程" class="headerlink" title="sequelize-cli的基本流程"></a>sequelize-cli的基本流程</h3><ul>
<li><p>sequelize中规定 模型的名称是单数、表的名称是复数</p>
</li>
<li><p>总置文件就是用来给数据库mock添加数据的文件</p>
<h3 id="生成文章表模型"><a href="#生成文章表模型" class="headerlink" title="生成文章表模型"></a>生成文章表模型</h3></li>
</ul>
<pre><code class="hljs javascript">sequelize model:generate --name Article --attributes  title:string,<span class="hljs-attr">desc</span>:string,<span class="hljs-attr">content</span>:text,<span class="hljs-attr">coverImg</span>:string,<span class="hljs-attr">status</span>:bigint</code></pre>

<p>生成一个模型名字叫<code>Article</code>的模型，有title、desc、content、coverImg、status五个字段，<code>sequelize</code>会自动为每张表添加<code>id</code>、<code>createdAt</code>、<code>updetedAt</code>字段。</p>
<h4 id="执行命令创建数据库-运行迁移"><a href="#执行命令创建数据库-运行迁移" class="headerlink" title="执行命令创建数据库 运行迁移"></a>执行命令创建数据库 运行迁移</h4><pre><code class="hljs javascript">sequelize db:migrate</code></pre>

<p>这个时候就会通过mysql发现已经建表成功了，并且拥有了这些字段，接下来就是本地如果需要模拟添加数据，需要运行总置文件</p>
<h5 id="新建一个总置文件"><a href="#新建一个总置文件" class="headerlink" title="新建一个总置文件"></a>新建一个总置文件</h5><pre><code class="hljs javascript">sequelize seed:generate --name article</code></pre>

<p><img src="https://api.lzwzw.cn/public/images/image-20200819111548140.png" srcset="/img/loading.gif" alt="image-20200819111548140"></p>
<p>然后就可以在这里面进行mock自己插入值了，替换掉pepple为表名，替换后面的数组对象为自己需要插入的对象即可</p>
<h5 id="运行迁移"><a href="#运行迁移" class="headerlink" title="运行迁移"></a>运行迁移</h5><pre><code class="hljs javascript">sequelize db:seed:all <span class="hljs-comment">//只有一个文件这样 多个的时候要加文件名 不然就全部文件都执行了 db:seed --seed 文件名字</span></code></pre>

<p>这样就添加了数据，刷新数据库已经可以看到数据了</p>
<h3 id="生成comment评论模型"><a href="#生成comment评论模型" class="headerlink" title="生成comment评论模型"></a>生成comment评论模型</h3><pre><code class="hljs javascript">sequelize model:generate --name Comment --attributes articleId:integer,<span class="hljs-attr">content</span>:text</code></pre>

<h5 id="运行迁移命令"><a href="#运行迁移命令" class="headerlink" title="运行迁移命令"></a>运行迁移命令</h5><pre><code class="hljs javascript">sequelize db:migrate</code></pre>

<h5 id="运行这个命令生成总置文件"><a href="#运行这个命令生成总置文件" class="headerlink" title="运行这个命令生成总置文件"></a>运行这个命令生成总置文件</h5><pre><code class="hljs javascript">sequelize seed:generate --name comment <span class="hljs-comment">//生成一个comment的总置文件</span></code></pre>

<p>有了就可以在seeders文件夹下打开添加数据了</p>
<h3 id="数据模型关联关系"><a href="#数据模型关联关系" class="headerlink" title="数据模型关联关系"></a>数据模型关联关系</h3><pre><code class="hljs javascript">A.hasOne(B); <span class="hljs-comment">// A 有一个 B</span>
A.belongsTo(B); <span class="hljs-comment">// A 属于 B</span>
A.hasMany(B); <span class="hljs-comment">// A 有多个 B</span>
A.belongsToMany(B, &#123; <span class="hljs-attr">through</span>: <span class="hljs-string">'C'</span> &#125;); <span class="hljs-comment">// A 属于多个 B , 通过联结表 C</span></code></pre>

<p>多种关系在<code>model</code>模型中定义 通过<code>associate</code></p>
<pre><code class="hljs javascript"><span class="hljs-built_in">module</span>.exports = <span class="hljs-function">(<span class="hljs-params">sequelize, DataTypes</span>) =&gt;</span> &#123;
  <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Article</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">Model</span> </span>&#123;
    <span class="hljs-comment">/**</span>
<span class="hljs-comment">     * Helper method for defining associations.</span>
<span class="hljs-comment">     * This method is not a part of Sequelize lifecycle.</span>
<span class="hljs-comment">     * The `models/index` file will call this method automatically.</span>
<span class="hljs-comment">     */</span>
    <span class="hljs-keyword">static</span> associate(models) &#123;
      <span class="hljs-comment">// 在此处定义关联 表示文章下面有很多评论</span>
      models.Article.hasMany(models.Comment)
      models.Article.belongsTo(models.Type)
    &#125;
  &#125;;
  Article.init(&#123;
    title: DataTypes.STRING,
    desc: DataTypes.STRING,
    content: DataTypes.TEXT,
    coverImg: DataTypes.STRING,
    status: DataTypes.BIGINT,
    typeId: DataTypes.BIGINT,
  &#125;, &#123;
    sequelize,
    modelName: <span class="hljs-string">'Article'</span>,
  &#125;);
  <span class="hljs-keyword">return</span> Article;
&#125;;</code></pre>

<p>可以添加多个关系，在使用的时候查询需要使用<code>include</code> 例如</p>
<pre><code class="hljs javascript">router.get(<span class="hljs-string">'/detail/:id'</span>, <span class="hljs-keyword">async</span> (req, res) =&gt; &#123;
    <span class="hljs-keyword">var</span> article = <span class="hljs-keyword">await</span> models.Article.findOne(&#123;
        where:&#123;<span class="hljs-attr">id</span>:req.params.id&#125;,
        include: [models.Comment,&#123;
            model:models.Type,
            attributes: [<span class="hljs-string">'className'</span>] <span class="hljs-comment">//限制只取某某字段 exclude排除</span>
          	<span class="hljs-comment">//attributes: &#123;exclude:['id']&#125;	排除某个字段</span>
        &#125;]
    &#125;) <span class="hljs-comment">//findByPk查找主键id</span>
    res.json(&#123;<span class="hljs-attr">data</span>:article&#125;)
&#125;)</code></pre>

<p><code>include</code>是一个数组，可以关联多个模型，也就是多张表的意思，里面的每一项为对象，<strong>attribute</strong>代表限制只需要<strong>目标模型</strong>的这几个字段即可，如果不限制，拿到的就是所有值。</p>
<h3 id="查询方法"><a href="#查询方法" class="headerlink" title="查询方法"></a>查询方法</h3><p>一般我们在使用<strong>*sequelize</strong>的方法查询时，一般语法是这样的：</p>
<pre><code class="hljs javascript"><span class="hljs-comment">/** 数据库模型.方法名(各种查询条件) */</span>

User.findOne(&#123;<span class="hljs-attr">where</span>:&#123;<span class="hljs-attr">username</span>:req.body.username&#125;&#125;)</code></pre>



<p>例如上方分为三部分，<strong>User</strong>为自己定义的数据库模型，其实也就代表用这个模型间接的操作数据库，<strong>findOne</strong>是查询方法，这里提供了多种方法，后面一一解释，在后面需要给这个方法传递的参数有很多，就是这种搜索的显示条件，<strong>where</strong>同sql语句里面的where一个意思。</p>
<ul>
<li><p>findOne  ===== 查询一条</p>
<pre><code class="hljs javascript">User.findOne(
  &#123;
    where: &#123; <span class="hljs-attr">username</span>: <span class="hljs-string">'小九'</span> &#125;
  &#125;
)</code></pre>

<p>上面表示用User模型查询一条数据，条件是<strong>username</strong>字段是小九，这就是查询单条数据</p>
</li>
<li><p>findAll  ===== 查询所有</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> result = <span class="hljs-keyword">await</span> Type.findAll()</code></pre>

<p>上面表示通过<strong>Type</strong>模型查询里面所有的数据，里面可以加条件，和上面一样。</p>
</li>
<li><p>findByPk  ===== 通过主键id查找</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> article = <span class="hljs-keyword">await</span> Article.findByPk(<span class="hljs-number">99</span>)</code></pre>

<p>上面表示通过<strong>Article</strong>模型查询主键id为99的这个数据，和查询单条数据区别不大，只是指定了查询字段为id</p>
</li>
<li><p>create  ===== 创建一条数据</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> user = <span class="hljs-keyword">await</span> models.User.create(req.body)</code></pre>

<p>上面是创建新增一条数据，拿到前端传来的数据存到数据库，其实就是新增一个用户的意思</p>
</li>
<li><p>destory   ===== 删除数据</p>
<pre><code class="hljs javascript">article.destroy( where: &#123; <span class="hljs-attr">username</span>: <span class="hljs-string">'小九'</span> &#125;)</code></pre>

<p>上面表示删除<strong>username</strong>为小九的一条数据。根据条件删除。</p>
</li>
<li><p>Update ==== 修改数据</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> article = <span class="hljs-keyword">await</span> models.Article.update(req.body, &#123;
        where: &#123; <span class="hljs-attr">name</span>:<span class="hljs-string">'小九'</span> &#125;
    &#125;)</code></pre>

<p>上面表述查询到<strong>name</strong>为小九的这个用户，修改他的名字为前端传来的数据<strong>req.body</strong>这个对象。</p>
</li>
<li><p>bulkCreate ==== 批量创建</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">const</span> user = <span class="hljs-keyword">await</span> User.bulkCreate([
  &#123; <span class="hljs-attr">name</span>: <span class="hljs-string">'Jack Sparrow'</span> &#125;,
  &#123; <span class="hljs-attr">name</span>: <span class="hljs-string">'Davy Jones'</span> &#125;
]);</code></pre>

<p>上面表示一次创建多条数据，这样的创建也会带来副作用，例如：性能低、不能对每一条数据都进行验证、</p>
</li>
</ul>
<hr>
<h3 id="进阶查询"><a href="#进阶查询" class="headerlink" title="进阶查询"></a>进阶查询</h3><p>上面就是普通的增删查改，但是实际业务远比这些复杂，学会了上面的就来试试下面的各种业务场景吧。</p>
<ul>
<li><p>findAndCountAll ==== 分页+模糊查询</p>
<pre><code class="hljs javascript">router.post(<span class="hljs-string">'/AllArticle'</span>, <span class="hljs-keyword">async</span> <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">req, res, next</span>) </span>&#123;
    <span class="hljs-keyword">let</span> currentPage = <span class="hljs-built_in">parseInt</span>(req.body.currentPage) || <span class="hljs-number">1</span>
    <span class="hljs-keyword">let</span> pageSize = <span class="hljs-built_in">parseInt</span>(req.body.pageSize) || <span class="hljs-number">5</span>
    <span class="hljs-keyword">let</span> where = &#123;&#125;;
    <span class="hljs-keyword">if</span> (req.body.typeId) &#123;
        where.typeId = req.body.typeId
    &#125;
    <span class="hljs-keyword">if</span>(req.body.status)&#123;
        where.status = req.body.status
    &#125;

    <span class="hljs-keyword">let</span> keyword = req.body.keyword

    <span class="hljs-keyword">if</span> (keyword) &#123;
        where.keyword = &#123;
            [Op.like]: <span class="hljs-string">'%'</span> + keyword + <span class="hljs-string">'%'</span>
        &#125;
    &#125;

    <span class="hljs-keyword">let</span> result = <span class="hljs-keyword">await</span> models.Article.findAndCountAll(&#123;
        order: [[<span class="hljs-string">'id'</span>, <span class="hljs-string">'DESC'</span>]], <span class="hljs-comment">//倒叙的方式输出 对比id 默认为ASC正序</span>
        where,   <span class="hljs-comment">//模糊查询的条件</span>
        offset: (currentPage - <span class="hljs-number">1</span>) * pageSize,
        limit: pageSize,
        include: [models.User, models.Type]
    &#125;)
   
    <span class="hljs-keyword">const</span> data = &#123;
        articles: result.rows,
        pagination: &#123;
            currentPage,
            pageSize,
            total: result.count
        &#125;
    &#125;
    <span class="hljs-keyword">new</span> Result(data, <span class="hljs-string">'获取成功'</span>).success(res)
&#125;);</code></pre>

<p>上面是我博客的一段真实场景代码，其作用是第一可以分页，第二倒序返回数据（这样可以后发表的博客显示在最前面），第三点可以支持模糊搜索，我们知道，前端一般采用分页，就需要总数，一页多少条，当前在第几页，这几个参数，所以这个方法会直接给你返回一个前端可以做分页的分页格式，那么我们看看查询条件，<strong>order</strong>为排序，上图表示以id为排序返回，正序倒序可以自己设定，根据场景而来，where是查询条件，模糊搜索需要满足一个条件，那么上面的语法表示，用户属于的这个关键词在我们的数据里面出现就会返回这条数据，这里的模糊搜索建议参考官网文档，场景不同模糊搜索的需求也就不一样了。其他三个字段就不用说了，前端传入的当前页，一页多少条，和总数，自行分析。</p>
</li>
<li><p>findOrCreate ==== 查询并新增</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> res = <span class="hljs-keyword">await</span> User.findOrCreate(&#123;<span class="hljs-attr">where</span>:&#123;<span class="hljs-attr">name</span>:<span class="hljs-string">'小九'</span>&#125;&#125;)</code></pre>

<p>上面表示查询<strong>username</strong>为小九的数据，如果没有就新增一条数据。在实际场景中，我们经常新增数据之前首先需要确认这个用户是否已经新增过了，所以这个方法就适用于这种场景</p>
</li>
<li><p>findAndDelete ==== 查询并删除</p>
<pre><code class="hljs javascript"><span class="hljs-keyword">let</span> res = <span class="hljs-keyword">await</span> User.findAndDelete(&#123;<span class="hljs-attr">where</span>:&#123;<span class="hljs-attr">name</span>:<span class="hljs-string">'小九'</span>&#125;&#125;)</code></pre>

<p>和上面的一样，查询这个用户是否存在，存在再进行删除，防止出现删除的用户在数据库中并不存在的这种操作，多人操作的情况下可能会出现这种情况，所以可以使用这个方法。</p>
</li>
</ul>
<h3 id="查询条件"><a href="#查询条件" class="headerlink" title="查询条件"></a>查询条件</h3><p>上面我们已经知道了基本的查询语法，但是实际业务中的查询可能更为麻烦，我们看看在<strong>sequelize</strong>中还提供了哪些参数吧：</p>
<pre><code class="hljs javascript">something.findOne(&#123;
  order: [
    <span class="hljs-comment">// 转义 username 并对查询结果按 DESC 方向排序</span>
    [<span class="hljs-string">'username'</span>, <span class="hljs-string">'DESC'</span>],

    <span class="hljs-comment">// 按 max(age) 排序</span>
    sequelize.fn(<span class="hljs-string">'max'</span>, sequelize.col(<span class="hljs-string">'age'</span>)),

    <span class="hljs-comment">// 按 max(age) DESC 排序</span>
    [sequelize.fn(<span class="hljs-string">'max'</span>, sequelize.col(<span class="hljs-string">'age'</span>)), <span class="hljs-string">'DESC'</span>],

    <span class="hljs-comment">// 按 otherfunction(`col1`, 12, 'lalala') DESC 排序</span>
    [sequelize.fn(<span class="hljs-string">'otherfunction'</span>, sequelize.col(<span class="hljs-string">'col1'</span>), <span class="hljs-number">12</span>, <span class="hljs-string">'lalala'</span>), <span class="hljs-string">'DESC'</span>],

    <span class="hljs-comment">// 按相关联的User 模型的 name 属性排序</span>
    [User, <span class="hljs-string">'name'</span>, <span class="hljs-string">'DESC'</span>],

    <span class="hljs-comment">// 按相关联的User 模型的 name 属性排序并将模型起别名为 Friend</span>
    [&#123;<span class="hljs-attr">model</span>: User, <span class="hljs-attr">as</span>: <span class="hljs-string">'Friend'</span>&#125;, <span class="hljs-string">'name'</span>, <span class="hljs-string">'DESC'</span>],

    <span class="hljs-comment">// 按相关联的User 模型的嵌套关联的 Company 模型的 name 属性排序</span>
    [User, Company, <span class="hljs-string">'name'</span>, <span class="hljs-string">'DESC'</span>],
  ]
  <span class="hljs-comment">// 以下所有声明方式都会视为字面量，应该小心使用</span>
  order: <span class="hljs-string">'convert(user_name using gbk)'</span>
  order: <span class="hljs-string">'username DESC'</span>
  order: sequelize.literal(<span class="hljs-string">'convert(user_name using gbk)'</span>)
&#125;)</code></pre>



<p>有时候我们查询出来的数据有很多，但是前端却不需要这么多数据，包括有的数据也不想暴露出去，那么我们如何对数据进行过滤呢？</p>
<pre><code class="hljs javascript">router.get(<span class="hljs-string">'/getDetail/:id'</span>, <span class="hljs-keyword">async</span> (req, res) =&gt; &#123;
    <span class="hljs-keyword">var</span> article = <span class="hljs-keyword">await</span> models.Article.findOne(&#123;
        where: &#123; <span class="hljs-attr">id</span>: req.params.id &#125;,
        attributes: [<span class="hljs-string">'title'</span>, <span class="hljs-string">'desc'</span>, <span class="hljs-string">'status'</span>, <span class="hljs-string">'userId'</span>, <span class="hljs-string">'typeId'</span>, <span class="hljs-string">'coverImg'</span>, <span class="hljs-string">'content'</span>],
        include: [models.Comment, &#123;
            model: models.Type,
            <span class="hljs-comment">// attributes: ['className'] //限制只取某某字段</span>
        &#125;]
    &#125;)
    <span class="hljs-keyword">new</span> Result(article, <span class="hljs-string">'获取成功'</span>).success(res)
&#125;)</code></pre>

<p>如上：使用<strong>attributes</strong>，在里面写入你需要返回的字段即可，其他字段就可以过滤掉了。</p>
<p>有时候我们需要多表联合查询，假设这样的场景，对于我的博客，会出现，一个分类下面有多篇文章，我需要查出这个分类和当前分类下的所有文章应该如何做呢？</p>
<pre><code class="hljs javascript">router.get(<span class="hljs-string">'/getTypeDetail/:id'</span>, <span class="hljs-keyword">async</span> (req, res) =&gt; &#123;
    <span class="hljs-keyword">var</span> types = <span class="hljs-keyword">await</span> models.Type.findOne(&#123;
        where:&#123;<span class="hljs-attr">id</span>:req.params.id&#125;,
        include: [models.Article]
    &#125;) <span class="hljs-comment">//findByPk查找主键id</span>
    res.json(&#123;<span class="hljs-attr">data</span>:types&#125;)
&#125;)</code></pre>

<p>使用<strong>include</strong>联合查询，后面表示需要一起查询的model，这里在定义的时候需要对齐关联，例如<code>models.Type.**hasMany(models.Article)</code>在定义model模型的时候进行关联，这句表示type模型的下面有很多的文章模型，翻译成业务就是，分类下面可以包含很多文章，这里的代码不方便贴了，建议查看官方文档。联合插叙相信在大的业务中肯定会很多涉及，也会相对复杂。我也入门很浅，就不误人子弟了。</p>

            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/node/">node</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/node/">node</a>
                    
                      <a class="hover-with-bg" href="/tags/sequelize/">sequelize</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/posts/840v59ba.html">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">SSR的优缺点</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/posts/a87jnd.html">
                        <span class="hidden-mobile">主题扩展（网站统计量自定义）</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
              <!-- Comments -->
              <div class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    function loadValine() {
      addScript('https://cdn.staticfile.org/valine/1.4.14/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "3PpvuTN5QkO4Tih7TqE1W5u8-gzGzoHsz",
          app_key: "2URikzfu2z1jtCXMp3Sy6Oin",
          placeholder: "说点什么",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "10",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    }
    createObserver(loadValine, 'vcomments');
  </script>
  <noscript>Please enable JavaScript to view the <a href="https://valine.js.org" target="_blank" rel="nofollow noopener noopener">comments
      powered by Valine.</a></noscript>


              </div>
            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->



<!-- <script>
//去掉畅言下面的跳转连接
  //  声明定时器
var timer = null
//  检查dom是否执行完成
function checkDom() {
    let dom = document.querySelector('.service-wrap-w')
    if(dom) {
      console.log(dom)
         //  执行dom加载完成后的操作
         dom.innerHTML = ''
        //  清除定时器
        if(!timer) {
            clearTimeout(timer)
        }
    } else {
        //  自我调用
        timer = setTimeout(checkDom, 0)
    }
}
//  首次执行
checkDom()
</script> -->
      
    </main>

    
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
    

    
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    

    

    <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <!-- <a  rel="nofollow noopener"><span class="motto">故事若不曲折怎么教人成长</span></a> -->

      <!-- <a rel="nofollow noopener"><span class="motto">xiaojiu</span></a> -->
      <!-- <i class="iconfont icon-love"></i> -->
      <a rel="nofollow noopener">

        <!-- <span class="motto"> 可惜故事太长只有风听我讲</span></a> -->
        
        <span class="motto"> 黄粱一梦、山水万重、人间总相逢</span></a>
        <!-- <span class="motto"> blog</span></a> -->

    </div>
    


    

    
    <div class="xiaojiu">
      <span id="xiaojiu_container_site_pv" style="display: none;">本站已经被访问- <span id="xiaojiu_value_site_pv"
          style="margin: 0 3px;"> </span> -次啦</span>
      <span id="xiaojiu_container_site_uv" style="display: none;">已经有- <span id="xiaojiu_value_site_uv"
          style="margin: 0 3px;"> </span> -个小伙伴来过啦</span>
    </div>


    <div class="date">
      <span id="timeDate">载入天数...</span>
      <span class="times" id="times">载入时分秒...
      </span>
      <img class="time" src="/img/sml.gif" srcset="/img/loading.gif" alt="">
      <script>
        var now = new Date();
        function createtime() {
          var grt = new Date("05/08/2018 00:00:00");//此处修改你的建站时间或者网站上线时间
          now.setTime(now.getTime() + 250);
          days = (now - grt) / 1000 / 60 / 60 / 24;
          dnum = Math.floor(days);
          hours = (now - grt) / 1000 / 60 / 60 - (24 * dnum);
          hnum = Math.floor(hours);
          if (String(hnum).length == 1) {
            hnum = "0" + hnum;
          }
          minutes = (now - grt) / 1000 / 60 - (24 * 60 * dnum) - (60 * hnum);
          mnum = Math.floor(minutes);
          if (String(mnum).length == 1) {
            mnum = "0" + mnum;
          }
          seconds = (now - grt) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum);
          snum = Math.round(seconds);
          if (String(snum).length == 1) {
            snum = "0" + snum;
          }
          document.getElementById("timeDate").innerHTML = "本站已运行&nbsp" + dnum + "&nbsp天";
          document.getElementById("times").innerHTML = hnum + "&nbsp小时&nbsp" + mnum + "&nbsp分&nbsp" + snum + "&nbsp秒";
        }
        setInterval("createtime()", 250);
      </script>
    </div>
</footer>
<!-- <script src="https://api.lzwzw.cn/public/js/xiaojiu.js"></script> -->
<!-- <script src="/js/xiaojiu.js"></script> -->
<script src="/js/message.js"></script>

<style>
  .xiaojiu {
    margin-top: 14px !important;
    background-image: linear-gradient(135deg, deeppink, dodgerblue);
    font-weight: bold;
    -webkit-background-clip: text;
    -moz-background-clip: text;
    background-clip: text;
    box-decoration-break: clone;
    -webkit-box-decoration-break: clone;
    -moz-box-decoration-break: clone;
    color: transparent;
    position: relative;
  }

  .date {
    background-image: linear-gradient(135deg, deeppink, dodgerblue);
    font-weight: bold;
    -webkit-background-clip: text;
    -moz-background-clip: text;
    background-clip: text;
    box-decoration-break: clone;
    -webkit-box-decoration-break: clone;
    -moz-box-decoration-break: clone;
    color: transparent;
    position: relative;
  }
</style>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/js/clipboard-use.js" ></script>







  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 6,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "sequelize基础Api&nbsp;",
      ],
      cursorChar: "✨",
      typeSpeed: 80,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
      icon: "❡"
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    var path = "/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>







  
  
    <script>
      !function (e, t, a) {
        function r() {
          for (var e = 0; e < s.length; e++) s[e].alpha <= 0 ? (t.body.removeChild(s[e].el), s.splice(e, 1)) : (s[e].y--, s[e].scale += .004, s[e].alpha -= .013, s[e].el.style.cssText = "left:" + s[e].x + "px;top:" + s[e].y + "px;opacity:" + s[e].alpha + ";transform:scale(" + s[e].scale + "," + s[e].scale + ") rotate(45deg);background:" + s[e].color + ";z-index:99999");
          requestAnimationFrame(r)
        }

        function n() {
          var t = "function" == typeof e.onclick && e.onclick;
          e.onclick = function (e) {
            t && t(), o(e)
          }
        }

        function o(e) {
          var a = t.createElement("div");
          a.className = "heart", s.push({
            el: a,
            x: e.clientX - 5,
            y: e.clientY - 5,
            scale: 1,
            alpha: 1,
            color: c()
          }), t.body.appendChild(a)
        }

        function i(e) {
          var a = t.createElement("style");
          a.type = "text/css";
          try {
            a.appendChild(t.createTextNode(e))
          } catch (t) {
            a.styleSheet.cssText = e
          }
          t.getElementsByTagName("head")[0].appendChild(a)
        }

        function c() {
          return "rgb(" + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + ")"
        }

        var s = [];
        e.requestAnimationFrame = e.requestAnimationFrame || e.webkitRequestAnimationFrame || e.mozRequestAnimationFrame || e.oRequestAnimationFrame || e.msRequestAnimationFrame || function (e) {
          setTimeout(e, 1e3 / 60)
        }, i(".heart{width: 10px;height: 10px;position: fixed;background: #f00;transform: rotate(45deg);-webkit-transform: rotate(45deg);-moz-transform: rotate(45deg);}.heart:after,.heart:before{content: '';width: inherit;height: inherit;background: inherit;border-radius: 50%;-webkit-border-radius: 50%;-moz-border-radius: 50%;position: fixed;}.heart:after{top: -5px;}.heart:before{left: -5px;}"), n(), r()
      }(window, document);
    </script>
  



  <script>(function (i, s, o, g, r, a, m) {
      i['DaoVoiceObject'] = r;
      i[r] = i[r] ||
        function () {
          (i[r].q = i[r].q || []).push(arguments);
        };
      i[r].l = 1 * new Date();
      a = s.createElement(o);
      m = s.getElementsByTagName(o)[0];
      a.async = 1;
      a.src = g;
      a.charset = 'utf-8';
      m.parentNode.insertBefore(a, m);
    })(window, document, 'script', ('https:' === document.location.protocol ? 'https:' : 'http:') + "//widget.daovoice.io/widget/2b2e276c.js", 'daovoice');
    daovoice('init', {
      app_id: "2b2e276c",
    });
    daovoice('update');
  </script>










  </div>
  
</body>

</html>
<script>

  // https://uploadbeta.com/api/pictures/random/?key=BingEverydayWallpaperPicture
  // https://source.unsplash.com/random  速度慢  有图还不错
  //https://uploadbeta.com/api/pictures/random/?key=%E6%8E%A8%E5%A5%B3%E9%83%8E 随机女图
  // https://api.jlongyan.cn/api/paper/random
  function checkWallaper() {
    fetch('https://source.unsplash.com/random', {
      method: 'GET',
      mode: 'cors',
    })
      .then(res => {
        setWallaper(res.url)
        localStorage.setItem('blogWallaper',res.url)
        return res.json()
      })
      // .then(res => {
      //   console.log(res);
      //   console.log("%c随机背景图片接口开放%c，随机壁纸api详情见博客,%c小九---!", "color:red", "", "color:orange;font-weight:bold")
      //   setWallaper(res.data.CoverImg)
      //   localStorage.setItem('blogWallaper',res.data.CoverImg)
      // })
      .catch(console.error)
  }

  if(localStorage.getItem('blogWallaper')){
    setWallaper(localStorage.getItem('blogWallaper'))
  }else{
    checkWallaper()
  }

  //设置壁纸
  function setWallaper(url){
    document.getElementById('indexBackground').style.backgroundImage = "url(" + url + ")";
  }

  let timer = null
  //  检查dom是否存在当前页面  监听 
  function watchYiyan() {
    let dom = document.getElementById('yiyan')
    if (dom) {
      fetch('https://v1.hitokoto.cn')
        .then(response => response.json())
        .then(data => {
          dom.innerText = data.hitokoto
        })
        .catch(console.error)
      if (!timer) {
        clearTimeout(timer)
      }
    } else {
      timer = setTimeout(watchYiyan, 0)
    }
  }
  //  首次执行
  watchYiyan()

  setTimeout(() => {
    clearTimeout(timer)
  }, 200);


</script>
